﻿using System;
using Dapper;
using System.Text;
using System.Linq;
using SilenthinkContract.Domain;

namespace SilenthinkService
{
    internal static partial class SqlTemplate
    {
        internal static class OrderTemplate
        {
            internal static SqlBuilder.Template GetOrder(string id, long? userId = null, bool inclItem = false,
                                                         bool inclTrade = false, bool inclNote = false)
            {
                var builder = new SqlBuilder();

                var sb = new StringBuilder();
                if (userId.HasValue && userId.Value > 0)
                {
                    sb.AppendFormat("SELECT TOP 1 * FROM {0} WHERE [Id]=@Id AND [UserId]=@UserId;",
                                    DatabaseSchema.OrderSchema.Table.Order);
                }
                else
                {
                    sb.AppendFormat("SELECT TOP 1 * FROM {0} WHERE [Id]=@Id;",
                                    DatabaseSchema.OrderSchema.Table.Order);
                }

                if (inclItem)
                {
                    sb.AppendFormat("SELECT * FROM {0} WHERE [OrderId]=@Id;",
                                    DatabaseSchema.OrderSchema.Table.OrderItem);
                }
                if (inclTrade)
                {
                    sb.AppendFormat("SELECT * FROM {0} WHERE [OrderId]=@Id;",
                                    DatabaseSchema.OrderSchema.Table.OrderTrade);
                }
                if (inclNote)
                {
                    sb.AppendFormat("SELECT * FROM {0} WHERE [OrderId]=@Id;",
                                    DatabaseSchema.OrderSchema.Table.OrderNote);
                }
                if (userId.HasValue && userId.Value > 0)
                {
                    return builder.AddTemplate(sb.ToString(), new { id, userId });

                }
                return builder.AddTemplate(sb.ToString(), new { id });
            }

            internal static SqlBuilder.Template GetPagedOrder(string username = null, long? userId = null, OrderStatus? orderStatus = null, PaymentStatus? paymentStatus = null, ShippingStatus? shippingStatus = null, DateTime? beginDate = null, DateTime? endDate = null, int index = 1, int size = 20)
            {
                if (index < 1)
                {
                    index = 1;
                }
                if (size < 0 || size > 200)
                {
                    size = 20;
                }

                var builder = new SqlBuilder().Field("*")
                    .Table(DatabaseSchema.OrderSchema.Table.Order)
                    .OrderBy("[CreatedOn] DESC")
                    .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });

                if (!string.IsNullOrEmpty(username))
                {
                    builder.Where("[UserName]=@UserName", new { username });
                }
                if (userId.HasValue && userId.Value > 0)
                {
                    builder.Where("[UserId]=@UserId", new { userId });
                }

                return builder.AddTemplate(BasicSingleTableSqlTemplate.PagedSelect);
            }

            internal static SqlBuilder.Template CreateOrder(Order o)
            {
                var builder = new SqlBuilder()
                        .Table(DatabaseSchema.OrderSchema.Table.Order)
                        .Field("([Id],[Category],[UserId],[UserName],[Quantity],[SubTotal],[ShippingCharge],[Discount],[Total],[Paid],[Refunded],[Weight],[TrackeCode],[ShippingMethod],[PaymentMethod],[PaidDate],[ShippingDate],[DeliveryDate],[OrderStatus],[ShippingStatus],[PaymentStatus],[Expiration],[IpAddress],[AssociatedId],[CreatedBy],[CreatedOn])VALUES(@Id,@UserId,@UserName,@Quantity,@SubTotal,@ShippingCharge,@Discount,@Total,@Paid,@Refunded,@Weight,@TraceCode,@ShippingMethod,@PaymentMethod,@PaidDate,@ShippingDate,@DeliveryDate,@OrderStatus,@ShippingStatus,@PaymentStatus,@Expiration,@IpAddress,@AssociatedId,@CreatedBy,@CreatedOn)")
                        .AddParameters(new { o.Id, o.Category, o.UserId, o.UserName, o.Quantity, o.SubTotal, o.ShippingCharge, o.Discount, o.Total, o.Paid, o.Refunded, o.Weight, o.TraceCode, o.ShippingMethod, o.PaymentMethod, o.PaidDate, o.ShippingDate, o.DeliveryDate, o.OrderStatus, o.ShippingStatus, o.PaymentStatus, o.Expiration, o.IpAddress, o.AssociatedId,  o.CreatedBy, o.CreatedOn });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
            }

            internal static SqlBuilder.Template CreateOrderItem(OrderItem[] o)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.OrderSchema.Table.OrderItem)
                    .Field("([Id],[UserId],[UserName],[OrderId],[ProductId],[ProductName],[ProductVariantId],[ProductVariantName],[Quantity],[Discount],[UnitPrice],[ProductAttribute],[ProductAttributeXml],[CreatedBy],[CreatedOn])VALUES(@Id,@UserId,@UserName,@OrderId,@ProductId,@ProductName,@ProductVariantId,@ProductVariantName,@Quantity,@Discount,@UnitPrice,@ProductAttribute,@ProductAttributeXml,@CreatedBy,@CreatedOn)");
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
            }
            internal static SqlBuilder.Template GetOrderItems(string[]orderIds)
            {
                var ids = orderIds.Aggregate((a, b) => "'"+a + "','" + b+"'").TrimEnd(',');
                var builder = new SqlBuilder().Field("*")
                    .Table(DatabaseSchema.OrderSchema.Table.OrderItem)
                    .Where("[OrderId] in (" + ids + ")");
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Select);
            }
            internal static SqlBuilder.Template GetAuctionOrderId(long userId, long auctionId)
            {
                var builder = new SqlBuilder().Field("[Id]")
                    .Table(DatabaseSchema.OrderSchema.Table.Order)
                    .Where("[OrderStatus]<2 AND [PaymentStatus]=0")
                    .Where("[UserId]=@UserId", new { userId })
                    .Where("AssociatedId=@AssociatedId", new { associatedId = auctionId.ToString() });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.SelectTopOne);
            }
        }
    }
}
